package cn.edu.bzu.aaa.dao;

import cn.edu.bzu.aaa.model.BusinessTripApproval;
import cn.edu.bzu.aaa.until.C3P0Util;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class BusinessTripApprovalDAO {

    /**
     * 获取所有商务出差审批记录。
     *
     * @return 包含所有商务出差审批记录的列表。
     */
    public List<BusinessTripApproval> getAll() {
        List<BusinessTripApproval> list = new ArrayList<>();
        try (Connection conn = C3P0Util.getConnection();
             PreparedStatement ps = conn.prepareStatement("SELECT * FROM businesstripapproval")) {
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                // 将每一行的数据填充到一个BusinessTripApproval对象中并加入列表
                list.add(populateBusinessTripApproval(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 根据ID获取特定的商务出差审批记录。
     *
     * @param id 商务出差审批记录的ID。
     * @return 对应ID的商务出差审批记录，如果不存在则返回null。
     */
    public BusinessTripApproval getByID(int id) {
        try (Connection conn = C3P0Util.getConnection();
             PreparedStatement ps = conn.prepareStatement("SELECT * FROM businesstripapproval WHERE ApprovalID = ?")) {
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                // 如果找到对应的记录，则填充并返回相应的对象
                return populateBusinessTripApproval(rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 添加新的商务出差审批记录。
     *
     * @param bta 新的商务出差审批记录对象。
     */
    public void add(BusinessTripApproval bta) {
        try (Connection conn = C3P0Util.getConnection();
             PreparedStatement ps = conn.prepareStatement(
                     "INSERT INTO businesstripapproval (UserID, TripDetails, Destination, TripType, Companions, Reason, StartDate, EndDate, NoticeFileLink, EstimatedCost, ApprovalStatus) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
                     Statement.RETURN_GENERATED_KEYS)) {
            // 设置SQL语句中的参数值
            ps.setInt(1, bta.getUserID());
            ps.setString(2, bta.getTripDetails());
            ps.setString(3, bta.getDestination());
            ps.setInt(4, bta.getTripType());
            ps.setString(5, bta.getCompanions());
            ps.setString(6, bta.getReason());
            ps.setDate(7, new java.sql.Date(bta.getStartDate().getTime()));
            ps.setDate(8, new java.sql.Date(bta.getEndDate().getTime()));
            ps.setString(9, bta.getNoticeFileLink());
            ps.setDouble(10, bta.getEstimatedCost());
            ps.setInt(11, bta.getApprovalStatus());

            ps.executeUpdate();

            // 获取自动生成的主键值，并设置给传入的对象
            ResultSet generatedKeys = ps.getGeneratedKeys();
            if (generatedKeys.next()) {
                bta.setApprovalID(generatedKeys.getInt(1));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 更新现有的商务出差审批记录。
     *
     * @param bta 要更新的商务出差审批记录对象。
     */
    public void update(BusinessTripApproval bta) {
        try (Connection conn = C3P0Util.getConnection();
             PreparedStatement ps = conn.prepareStatement(
                     "UPDATE businesstripapproval SET UserID=?, TripDetails=?, Destination=?, TripType=?, Companions=?, Reason=?, StartDate=?, EndDate=?, NoticeFileLink=?, EstimatedCost=?, ApprovalStatus=? WHERE ApprovalID=?")) {
            // 设置SQL语句中的参数值
            ps.setInt(1, bta.getUserID());
            ps.setString(2, bta.getTripDetails());
            ps.setString(3, bta.getDestination());
            ps.setInt(4, bta.getTripType());
            ps.setString(5, bta.getCompanions());
            ps.setString(6, bta.getReason());
            ps.setDate(7, new java.sql.Date(bta.getStartDate().getTime()));
            ps.setDate(8, new java.sql.Date(bta.getEndDate().getTime()));
            ps.setString(9, bta.getNoticeFileLink());
            ps.setDouble(10, bta.getEstimatedCost());
            ps.setInt(11, bta.getApprovalStatus());
            ps.setInt(12, bta.getApprovalID());

            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 删除指定ID的商务出差审批记录。
     *
     * @param id 要删除的商务出差审批记录的ID。
     */
    public void delete(int id) {
        try (Connection conn = C3P0Util.getConnection();
             PreparedStatement ps = conn.prepareStatement("DELETE FROM businesstripapproval WHERE ApprovalID = ?")) {
            ps.setInt(1, id);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 从ResultSet中填充并返回一个新的BusinessTripApproval对象。
     *
     * @param rs ResultSet对象，包含一行数据。
     * @return 填充后的BusinessTripApproval对象。
     * @throws SQLException 如果读取ResultSet时发生错误。
     */
    private BusinessTripApproval populateBusinessTripApproval(ResultSet rs) throws SQLException {
        BusinessTripApproval bta = new BusinessTripApproval();
        bta.setApprovalID(rs.getInt("ApprovalID"));
        bta.setUserID(rs.getInt("UserID"));
        bta.setTripDetails(rs.getString("TripDetails"));
        bta.setDestination(rs.getString("Destination"));
        bta.setTripType(rs.getInt("TripType"));
        bta.setCompanions(rs.getString("Companions"));
        bta.setReason(rs.getString("Reason"));
        bta.setStartDate(rs.getDate("StartDate"));
        bta.setEndDate(rs.getDate("EndDate"));
        bta.setNoticeFileLink(rs.getString("NoticeFileLink"));
        bta.setEstimatedCost(rs.getDouble("EstimatedCost"));
        bta.setApprovalStatus(rs.getInt("ApprovalStatus"));
        return bta;
    }
}